#!/bin/bash
# ========================================
# RedFire MySQL监控配置脚本
# 版本: v5.0.0 (2025-09-16)
# 功能: 配置性能监控和日志
# ========================================

set -e

echo "=========================================="
echo "开始配置RedFire MySQL监控..."
echo "=========================================="

# 等待MySQL完全启动
while ! mysqladmin ping -h localhost -u root -p"${MYSQL_ROOT_PASSWORD}" --silent; do
    echo "等待MySQL启动中..."
    sleep 2
done

# 配置监控
mysql -u root -p"${MYSQL_ROOT_PASSWORD}" <<EOF

-- ========================================
-- 启用Performance Schema
-- ========================================

-- 检查Performance Schema状态
SELECT 
    VARIABLE_NAME as '配置项',
    VARIABLE_VALUE as '当前值'
FROM performance_schema.global_variables 
WHERE VARIABLE_NAME IN (
    'performance_schema',
    'performance_schema_max_table_instances',
    'performance_schema_max_table_handles'
)
ORDER BY VARIABLE_NAME;

-- ========================================
-- 配置慢查询日志
-- ========================================

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_slow_admin_statements = 'ON';

-- 显示慢查询配置
SELECT 
    VARIABLE_NAME as '慢查询配置',
    VARIABLE_VALUE as '值'
FROM performance_schema.global_variables 
WHERE VARIABLE_NAME IN (
    'slow_query_log',
    'long_query_time',
    'log_queries_not_using_indexes',
    'log_slow_admin_statements',
    'slow_query_log_file'
)
ORDER BY VARIABLE_NAME;

-- ========================================
-- 配置二进制日志
-- ========================================

-- 显示二进制日志配置
SELECT 
    VARIABLE_NAME as '二进制日志配置',
    VARIABLE_VALUE as '值'
FROM performance_schema.global_variables 
WHERE VARIABLE_NAME IN (
    'log_bin',
    'binlog_format',
    'sync_binlog',
    'expire_logs_days',
    'max_binlog_size'
)
ORDER BY VARIABLE_NAME;

-- ========================================
-- 创建监控视图
-- ========================================

USE redfire_trading;

-- 创建实时连接监控视图
CREATE OR REPLACE VIEW v_connection_monitor AS
SELECT 
    PROCESSLIST_ID as connection_id,
    PROCESSLIST_USER as user,
    PROCESSLIST_HOST as host,
    PROCESSLIST_DB as database_name,
    PROCESSLIST_COMMAND as command,
    PROCESSLIST_TIME as time_seconds,
    PROCESSLIST_STATE as state,
    LEFT(PROCESSLIST_INFO, 100) as query_preview
FROM performance_schema.processlist
WHERE PROCESSLIST_USER NOT IN ('root', 'mysql.session', 'mysql.sys')
ORDER BY PROCESSLIST_TIME DESC;

-- 创建表空间使用监控视图
CREATE OR REPLACE VIEW v_table_space_monitor AS
SELECT 
    TABLE_SCHEMA as database_name,
    TABLE_NAME as table_name,
    ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as size_mb,
    ROUND((DATA_LENGTH / 1024 / 1024), 2) as data_mb,
    ROUND((INDEX_LENGTH / 1024 / 1024), 2) as index_mb,
    TABLE_ROWS as estimated_rows,
    CREATE_TIME as created_time,
    UPDATE_TIME as last_update_time
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'redfire_trading'
    AND TABLE_TYPE = 'BASE TABLE'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

-- 创建InnoDB状态监控视图
CREATE OR REPLACE VIEW v_innodb_monitor AS
SELECT 
    VARIABLE_NAME as metric_name,
    VARIABLE_VALUE as metric_value,
    CASE 
        WHEN VARIABLE_NAME LIKE '%buffer_pool%' THEN 'Buffer Pool'
        WHEN VARIABLE_NAME LIKE '%lock%' THEN 'Locks'
        WHEN VARIABLE_NAME LIKE '%log%' THEN 'Logs'
        WHEN VARIABLE_NAME LIKE '%row%' THEN 'Row Operations'
        WHEN VARIABLE_NAME LIKE '%page%' THEN 'Pages'
        ELSE 'Other'
    END as category
FROM performance_schema.global_status 
WHERE VARIABLE_NAME REGEXP '^Innodb_'
    AND VARIABLE_NAME NOT LIKE '%lsn%'
ORDER BY category, VARIABLE_NAME;

-- 创建查询性能监控视图
CREATE OR REPLACE VIEW v_query_performance_monitor AS
SELECT 
    DIGEST_TEXT as query_pattern,
    COUNT_STAR as execution_count,
    ROUND(AVG_TIMER_WAIT / 1000000000000, 4) as avg_execution_time_sec,
    ROUND(MAX_TIMER_WAIT / 1000000000000, 4) as max_execution_time_sec,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 4) as total_execution_time_sec,
    SUM_ROWS_EXAMINED as total_rows_examined,
    SUM_ROWS_SENT as total_rows_sent,
    FIRST_SEEN as first_seen,
    LAST_SEEN as last_seen
FROM performance_schema.events_statements_summary_by_digest 
WHERE SCHEMA_NAME = 'redfire_trading'
    AND COUNT_STAR > 0
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;

-- ========================================
-- 创建监控存储过程
-- ========================================

DELIMITER $$

-- 系统状态检查存储过程
DROP PROCEDURE IF EXISTS CheckSystemStatus$$
CREATE PROCEDURE CheckSystemStatus()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    
    -- 显示系统基本信息
    SELECT '系统基本信息' as info_type;
    SELECT 
        @@version as mysql_version,
        @@version_comment as mysql_comment,
        @@hostname as hostname,
        NOW() as current_time,
        @@uptime as uptime_seconds;
    
    -- 显示连接信息
    SELECT '连接信息' as info_type;
    SELECT 
        VARIABLE_VALUE as max_connections
    FROM performance_schema.global_variables 
    WHERE VARIABLE_NAME = 'max_connections';
    
    SELECT 
        COUNT(*) as current_connections
    FROM performance_schema.processlist;
    
    -- 显示InnoDB状态
    SELECT 'InnoDB状态' as info_type;
    SELECT 
        VARIABLE_NAME,
        VARIABLE_VALUE
    FROM performance_schema.global_status 
    WHERE VARIABLE_NAME IN (
        'Innodb_buffer_pool_pages_total',
        'Innodb_buffer_pool_pages_free',
        'Innodb_buffer_pool_pages_dirty',
        'Innodb_rows_read',
        'Innodb_rows_inserted',
        'Innodb_rows_updated',
        'Innodb_rows_deleted'
    );
    
    -- 显示慢查询统计
    SELECT '慢查询统计' as info_type;
    SELECT 
        VARIABLE_VALUE as slow_queries_count
    FROM performance_schema.global_status 
    WHERE VARIABLE_NAME = 'Slow_queries';
    
END$$

-- 表统计信息存储过程
DROP PROCEDURE IF EXISTS GetTableStats$$
CREATE PROCEDURE GetTableStats()
BEGIN
    SELECT 
        TABLE_NAME as table_name,
        TABLE_ROWS as estimated_rows,
        ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as size_mb,
        ROUND((DATA_LENGTH / 1024 / 1024), 2) as data_mb,
        ROUND((INDEX_LENGTH / 1024 / 1024), 2) as index_mb,
        CREATE_TIME as created_time,
        UPDATE_TIME as last_update_time
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = 'redfire_trading'
        AND TABLE_TYPE = 'BASE TABLE'
    ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;
END$$

DELIMITER ;

-- ========================================
-- 验证监控配置
-- ========================================

-- 显示创建的监控视图
SELECT 
    TABLE_NAME as view_name,
    TABLE_COMMENT as description
FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA = 'redfire_trading'
    AND TABLE_NAME LIKE 'v_%monitor%'
ORDER BY TABLE_NAME;

-- 显示创建的存储过程
SELECT 
    ROUTINE_NAME as procedure_name,
    ROUTINE_TYPE as type,
    CREATED as created_time
FROM information_schema.ROUTINES 
WHERE ROUTINE_SCHEMA = 'redfire_trading'
    AND ROUTINE_NAME LIKE '%Status%'
    OR ROUTINE_NAME LIKE '%Stats%'
ORDER BY ROUTINE_NAME;

EOF

echo "=========================================="
echo "RedFire MySQL监控配置完成！"
echo "创建的监控组件："
echo "  - v_connection_monitor: 连接监控视图"
echo "  - v_table_space_monitor: 表空间监控视图"
echo "  - v_innodb_monitor: InnoDB状态监控视图"
echo "  - v_query_performance_monitor: 查询性能监控视图"
echo "  - CheckSystemStatus(): 系统状态检查存储过程"
echo "  - GetTableStats(): 表统计信息存储过程"
echo "=========================================="
